package com.ruoyi.common.core.utils.poi;

import java.io.*;
import java.util.*;

import com.monitorjbl.xlsx.StreamingReader;
import com.ruoyi.common.core.utils.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.*;

public class ExcelDyUtil {
    /**
     * 数据导出成 Excel 数据格式
     *
     * @param headList 表头信息 （为null 或者 size() == 0 证明不存在）
     * @param dataList 数据信息
     * @return 结果数组
     */
    public static byte[] exportExcel(List<String> headList, List<Map<String, Object>> dataList, String xlsType) {
        byte[] bytes = null;
        try (Workbook workbook = StringUtils.endsWithIgnoreCase(xlsType, "xlsx") ? new XSSFWorkbook() : new HSSFWorkbook();
             ByteArrayOutputStream os = new ByteArrayOutputStream()) {
            int size = 65530;
            int sheetCount = dataList == null ? 0 : (dataList.size() < size ? 1 : (dataList.size() % size > 0 ? (dataList.size() / size) + 1 : dataList.size() / size));
            for (int i = 0; i < sheetCount; i++) {
                Sheet sheet = workbook.createSheet("sheet" + i);
                sheet.setDefaultColumnWidth(13);
                int rowIndex = 0;
                if (headList != null && headList.size() > 0) {
                    writerHeader(headList, sheet);
                    rowIndex++;
                }
                int end = dataList.size() > (size * (i + 1)) ? size : dataList.size();
                writerData(dataList.subList(i * size, end), headList, sheet, rowIndex);
            }
            workbook.write(os);
            bytes = os.toByteArray();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return bytes;
    }

    // 写入本地文件  防止内存溢出
    public static void write(List<String> headList, List<Map<String, Object>> dataList, String xlsxPath, Integer size) {
        File file = new File(xlsxPath);
        try (SXSSFWorkbook workbook = new SXSSFWorkbook();
             FileOutputStream outputStream = new FileOutputStream(file)) {
            int sheetCount = dataList == null ? 0 : (dataList.size() < size ? 1 : (dataList.size() % size > 0 ? (dataList.size() / size) + 1 : dataList.size() / size));
            for (int i = 0; i < sheetCount; i++) {
                Sheet sheet = workbook.createSheet(i + "");
                sheet.setDefaultColumnWidth(13);
                int rowIndex = 0;
                if (headList != null && headList.size() > 0) {
                    writerHeader(headList, sheet);
                    rowIndex++;
                }
                int end = dataList.size() > (size * (i + 1)) ? size : dataList.size();
                writerData(dataList.subList(i * size, end), headList, sheet, rowIndex);
            }
            workbook.write(outputStream);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //读取本地文件 防止内存溢出
    public static List<Map<String, Object>> readExcel(File file, Integer size, Integer current) {
        try (InputStream is = new FileInputStream(file);
             Workbook workbook = StreamingReader.builder()
                     .rowCacheSize(100)
                     .bufferSize(4096)
                     .open(is)) {
            List<String> headerList = new ArrayList<>();
            List<Map<String, Object>> dataList = new ArrayList<>();
            for (Sheet sheet : workbook) {
                int start = (current * size) + 1;
                int end = start + 10;
                int i = 0;
                for (Row row : sheet) {
                    if (i == 0) {
                        for (Cell cell : row) {
                            headerList.add(cell.getStringCellValue());
                        }
                    } else if (i > start && i <= end) {
                        Map<String, Object> map = new HashMap<>();
                        int j = 0;
                        for (Cell cell : row) {
                            map.put(headerList.get(j), cell.getStringCellValue());
                            j++;
                        }
                        dataList.add(map);
                    } else if (i > end) {
                        break;
                    }
                    i++;
                }
            }
            return dataList;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public static byte[] exportExcelHead(List<String> headList, String xlsType) {
        byte[] bytes = null;
        try (Workbook workbook = StringUtils.endsWithIgnoreCase(xlsType, "xlsx") ? new XSSFWorkbook() : new HSSFWorkbook();
             ByteArrayOutputStream os = new ByteArrayOutputStream()) {
            Sheet sheet = workbook.createSheet(0 + "");
            sheet.setDefaultColumnWidth(13);
            if (headList != null && headList.size() > 0) {
                writerHeader(headList, sheet);
            }
            workbook.write(os);
            bytes = os.toByteArray();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return bytes;
    }

    /**
     * 写表头
     *
     * @param headList 表头List
     * @param sheet    sheet
     */
    public static void writerHeader(List<String> headList, Sheet sheet) {
        Row row = sheet.createRow(0);
        row.setHeightInPoints(21);
        RichTextString text;
        for (int i = 0; i < headList.size(); i++) {
            Cell cell = row.createCell((short) i);
            text = new XSSFRichTextString(headList.get(i));
            cell.setCellValue(text.toString());
        }
    }

    /**
     * 写数据
     *
     * @param dataList 数据
     * @param headList 表头
     * @param sheet    sheel
     * @param rowIndex 单元格索引
     */
    public static void writerData(List<Map<String, Object>> dataList, List<String> headList, Sheet sheet, int rowIndex) {
        Row row;
        Cell cell;
        RichTextString text;
        for (int i = 0; i < dataList.size(); i++) {
            row = sheet.createRow(rowIndex + i);
            Map<String, Object> datas = dataList.get(i);
            for (int j = 0; j < headList.size(); j++) {
                cell = row.createCell(j);
                Object value = datas.get(headList.get(j));
                String data = value == null ? "" : String.valueOf(value);
                text = new XSSFRichTextString(data);
                cell.setCellValue(text);
            }
        }
    }

    public static Object getValue(Cell cell) {
        Object cellValue = "";
        if (cell != null) {
            //必须使用switch来获取单元格数据，否则会报错，除非确保execl所有单元格的数据类型一致
            switch (cell.getCellType()) {
                case BOOLEAN://获取布尔型数据
                    cellValue = cell.getBooleanCellValue();
                    break;
                case BLANK://获取空值
                    cellValue = cell.getBooleanCellValue();
                    break;
                case ERROR://获取错误单元格
                    cellValue = cell.getErrorCellValue();
                    break;
                case NUMERIC://获取数字类型的数据
                    cellValue = cell.getNumericCellValue();
                    break;
                case STRING://获取字符串
                    cellValue = cell.getStringCellValue();
                    break;
            }
        } else {
            cellValue = "";
        }
        return cellValue;
    }
}
